package com.sie.project.mapper;


import com.sie.project.entity.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;


/**
 * @author linjinde
 */
@Mapper
@Repository
public interface ProjectMapper {
    @Select("SELECT\n" +
            "\tproject.puid puid,\n" +
            "\tproject.PPROJECT_ID project_id,\n" +
            "\tproject.PPROJECT_NAME project_name,\n" +
            "\tproject.PPROJECT_DESC project_desc,\n" +
            "\tproject.PX8_PROJECT_CLASS project_class,\n" +
            "\tproject.PX8_PROJECT_TYPE project_type,\n" +
            "\tproject.PX8_CUSTOMER_INFO customer_info,\n" +
            "\tproject.PX8_PROJECT_AREA project_area,\n" +
            "\tproject.PX8_IMPORTANT_OR_NOT important_or_not,\n" +
            "\tproject.PX8_NEWENGRY_OR_NOT newengry_or_not,\n" +
            "\tproject.PX8_FIXED_OR_NOT fixed_or_not,\n" +
            "\tpuser.PUSER_NAME user_name,\n" +
            "\tproject.PX8_BUSINESS_MANAGER business_manager,\n" +
            "\tproject.px8_product_type product_type,\n" +
            "\tproject.px8_product_specification product_specification,\n" +
            "\tproject.px8_product_use product_use,\n" +
            "\tproject.px8_product_feature product_feature,\n" +
            "\tproject.px8_process_feature process_feature,\n" +
            "\tproject.px8_main_material main_material,\n" +
            "\tproject.px8_product_power product_power,\n" +
            "\tproject.px8_customer_drawing_num customer_drawing_num\n" +
            "FROM\n" +
            "\t\tPTC_PROJECT project\n" +
            "\t\tLEFT JOIN ppom_user puser on puser.PUID = project.RX8_PROJECT_MANAGERU\n" +
            " WHERE\n" +
            "project.PIS_ACTIVE = '1' " +
            " \t\t AND project.PX8_PROJECT_TYPE = #{type}")
    List<Project> getProjectsByType(String type);

    @Select("SELECT\n" +
            "\tproject.puid puid,\n" +
            "\tproject.PPROJECT_ID project_id,\n" +
            "\tproject.PPROJECT_NAME project_name,\n" +
            "\tproject.PPROJECT_DESC project_desc,\n" +
            "\tproject.PX8_PROJECT_CLASS project_class,\n" +
            "\tproject.PX8_PROJECT_TYPE project_type,\n" +
            "\tproject.PX8_CUSTOMER_INFO customer_info,\n" +
            "\tproject.PX8_PROJECT_AREA project_area,\n" +
            "\tproject.PX8_IMPORTANT_OR_NOT important_or_not,\n" +
            "\tproject.PX8_NEWENGRY_OR_NOT newengry_or_not,\n" +
            "\tproject.PX8_FIXED_OR_NOT fixed_or_not,\n" +
            "\tpuser.PUSER_NAME user_name,\n" +
            "\tproject.PX8_BUSINESS_MANAGER business_manager,\n" +
            "\tproject.px8_product_type product_type,\n" +
            "\tproject.px8_product_specification product_specification,\n" +
            "\tproject.px8_product_use product_use,\n" +
            "\tproject.px8_product_feature product_feature,\n" +
            "\tproject.px8_process_feature process_feature,\n" +
            "\tproject.px8_main_material main_material,\n" +
            "\tproject.px8_product_power product_power,\n" +
            "\tproject.px8_customer_drawing_num customer_drawing_num\n" +
            "FROM\n" +
            "\t\tPTC_PROJECT project\n" +
            "\t\tLEFT JOIN ppom_user puser on puser.PUID = project.RX8_PROJECT_MANAGERU\n" +
            " WHERE\n" +
            "project.PIS_ACTIVE = '1' " +
            " \t\tAND project.PPROJECT_ID = #{projectId}\n" +
            "\t")
    List<Project> getProjectDetail(String projectId);

    @Select("SELECT\n" +
            "\tPUID PUID,\n" +
            "\tPPROJECT_ID project_id,\n" +
            "\tPPROJECT_NAME object_name,\n" +
            "\tPPROJECT_DESC project_desc,\n" +
            "\tPX8_PROJECT_TYPE prodcution_type,\n" +
            "\tPX8_PROJECT_CLASS_CODE project_class_code,\n" +
            "\tPX8_PROJECT_CLASS project_class,\n" +
            "\tPX8_PROGRAM_ID program_id,\n" +
            "\tPX8_PRODUCT_SPECIFICATION product_specification,\n" +
            "\tPX8_IMPORTANT_OR_NOT important_or_not,\n" +
            "\tPX8_CUSTOMER_INFO customer_info,\n" +
            "\tPX8_PROJECT_AREA project_area,\n" +
            "\tPX8_PRODUCT_USE product_use,\n" +
            "\tRX8_PROJECT_MANAGERU project_manager_id,\n" +
            "\tPX8_PROJECT_AREA_CODE project_area_code,\n" +
            "\tPX8_PRODUCT_USE_CODE product_use_code,\n" +
            "\tPX8_PRODUCT_POWER product_power,\n" +
            "\tPX8_PRODUCT_FEATURE product_feature,\n" +
            "\tPX8_PRODCUTION_TYPE_CODE prodcution_type_code,\n" +
            "\tPX8_PRODCUTION_TYPE prodcution_type,\n" +
            "\tPX8_PROCESS_FEATURE process_feature,\n" +
            "\tPX8_NEWENGRY_OR_NOT newengry_or_not,\n" +
            "\tPX8_MAIN_MATERIAL main_material,\n" +
            "\tPX8_FIXED_OR_NOT fixed_or_not,\n" +
            "\tPX8_CUSTOMER_MANAGER customer_manager,\n" +
            "\tPX8_CUSTOMER_DRAWING_NUM customer_drawing_num,\n" +
            "\tPX8_BUSINESS_MANAGER business_manager \n" +
            "FROM\n" +
            "\tPTC_PROJECT \n" +
            "WHERE\n" +
            "project.PIS_ACTIVE = '1' " +
            "\tAND PPROJECT_ID = #{projectId}")
    List<Project> getProjectsById(String projectId);


    @Select("SELECT\n" +
//            "    EXTRACT(YEAR FROM work.PCREATION_DATE) AS \"Year\",\n" +
            "    EXTRACT(MONTH FROM work.PCREATION_DATE) AS \"Month\",\n" +
            "    COUNT(*) AS \"Count\"\n" +
            "FROM\n" +
            "    PTC_PROJECT project\n" +
            "    LEFT JOIN ppom_application_object work ON work.puid = project.puid\n" +
            "WHERE\n" +
            "project.PIS_ACTIVE = '1' " +
            " AND project.PX8_PROJECT_TYPE = #{projectType} \n" +
            " AND project.PX8_PROJECT_CLASS = #{projectClass} \n" +
            " AND EXTRACT(YEAR FROM work.PCREATION_DATE) = #{year} \n" +
            "GROUP BY\n" +
            "    EXTRACT(YEAR FROM work.PCREATION_DATE),\n" +
            "    EXTRACT(MONTH FROM work.PCREATION_DATE)\n")
    List<MonthCountDTO> getMonthlyCounts(int year, String projectType,String projectClass);
    @Select("SELECT\n" +
//            "    EXTRACT(YEAR FROM work.PCREATION_DATE) AS \"Year\",\n" +
            "    EXTRACT(MONTH FROM work.PCREATION_DATE) AS \"Month\",\n" +
            "    COUNT(*) AS \"Count\"\n" +
            "FROM\n" +
            "    PTC_PROJECT project\n" +
            "    LEFT JOIN ppom_application_object work ON work.puid = project.puid\n" +
            "WHERE\n" +
            "project.PIS_ACTIVE = '1' " +
            "  AND  project.PX8_PROJECT_TYPE = #{projectType} \n" +
            " AND EXTRACT(YEAR FROM work.PCREATION_DATE) = #{year} \n" +
            "GROUP BY\n" +
            "    EXTRACT(YEAR FROM work.PCREATION_DATE),\n" +
            "    EXTRACT(MONTH FROM work.PCREATION_DATE)\n")
    List<MonthCountDTO> getMonthlyCountsByAll(int year, String projectType);


    @Select("SELECT\n" +
            "\tPUID puid,\n" +
            "\tPPROJECT_ID project_id,\n" +
            "\tPPROJECT_NAME project_name \n" +
            "FROM\n" +
            "\tPTC_PROJECT where PTC_PROJECT.PIS_ACTIVE = '1' ")
    List<ProjectSelectVo> getProjectSelectVoList();

    @Select("SELECT\n" +
            "\tproject.puid puid,\n" +
            "\tproject.PPROJECT_ID project_id,\n" +
            "\tproject.PPROJECT_NAME project_name,\n" +
            "\tproject.PX8_PROJECT_CLASS project_class,\n" +
            "\tproject.PX8_PROJECT_TYPE project_type,\n" +
            "\ttask.PFINISH_DATE finish_date,\n" +
            "\ttask.PSTART_DATE start_date,\n" +
            "\ttask.PACTUAL_START_DATE act_start_date,\n" +
            "\ttask.PACTUAL_FINISH_DATE act_finish_date,\n" +
            "\ttask.PTASK_TYPE task_type,\n" +
            "\ttask.PFND0STATUS project_status,\n" +
            "\tpwo.POBJECT_NAME task_name,\n" +
            "\tpuser.PUSER_NAME user_name\n" +
            "FROM\n" +
            "\t\tPTC_PROJECT project\n" +
            "\t\tLEFT JOIN PPROJECT_LIST plist ON plist.PVALU_0 = project.PUID\n" +
            "\t\tLEFT JOIN PSCHEDULE she on she.puid  = \tplist.puid \n" +
            "\t\tLEFT JOIN PSCHEDULETASK task on she.puid = task.RSCHEDULE_TAGU \n" +
            "\t\tLEFT JOIN PWORKSPACEOBJECT pwo on pwo.puid = task.puid\n" +
            " \t\tLEFT JOIN ppom_user puser on puser.PUID = task.RPRIVILEGED_USERU\n" +
            "\t\tWHERE project.PPROJECT_ID = #{projectid} \n"+
            "\t\tAND project.PIS_ACTIVE = '1' \n")
    List<ProjectTaskDTO> getProjectTasks(String projectid);

    @Select("SELECT DISTINCT\n" +
            "\tproject.puid puid,\n" +
            "\tproject.PPROJECT_ID project_id,\n" +
            "\tpwo.POBJECT_NAME risk_name,\n" +
            "\trisk.PX8_RISK_LEVEL risk_level,\n" +
            "\trisk.PX8_RISK_INFLUENCE risk_influence,\n" +
            "\trisk.PX8_PLAN_CLOSEDATE plan_closedate,\n" +
            "\trisk.PX8_ACTUAL_CLOSEDATE act_closedate,\n" +
            "\trisk.PX8_RESPONESOR responesor,\n" +
            "\triskso.PVAL_0 solution\n" +
            "FROM\n" +
            "\t\tPTC_PROJECT project\n" +
            "\t\tLEFT JOIN PPROJECT_LIST plist ON plist.PVALU_0 = project.PUID\n" +
            "\t\tLEFT JOIN PIMANRELATION iman on iman.RPRIMARY_OBJECTU = project.PUID\n" +
            " \t\tLEFT JOIN PFORM fo on fo.puid = iman.RSECONDARY_OBJECTU\n" +
            "\t\tLEFT JOIN PX8_PROJECT_RISKSTORAGE risk on risk.puid = fo.RDATA_FILEU\n" +
            "\t\tLEFT JOIN PWORKSPACEOBJECT pwo on pwo.puid = fo.puid\n" +
            "\t\tLEFT JOIN PX8_RISK_SOLUTION riskso on riskso.puid = risk.puid\n" +
            "WHERE project.PPROJECT_ID =#{projectid}  and risk.puid is not null \n" +
            "\t\tAND project.PIS_ACTIVE = '1' \n")
    List<ProjectRisk> getRiskList(String projectId);
    @Select("SELECT DISTINCT\n" +
            "\tproject.puid puid,\n" +
            "\tproject.PPROJECT_ID project_id,\n" +
            "\tpwo.POBJECT_NAME ir_name,\n" +
            "\tpwo.POBJECT_DESC ir_desc,\n" +
            "\tir.PX8_IR_STATUS status,\n" +
            "\tir.PX8_PRIORITY_LEVEL ir_level,\n" +
            "\tir.PX8_PROJECT_STAGE project_stage,\n" +
            "\tir.PX8_RESPONESOR responesor,\n" +
            "\tir.PX8_PLAN_CLOSEDATE plan_closedate,\n" +
            "\tir.PX8_ANALYSIS analysis,\n" +
            "\tshort.PVAL_0 short_solution,\n" +
            "\tlongs.PVAL_0 long_solution" +
            "\t\n" +
            "FROM\n" +
            "\t\tPTC_PROJECT project\n" +
            "\t\tLEFT JOIN PPROJECT_LIST plist ON plist.PVALU_0 = project.PUID\n" +
            "\t\tLEFT JOIN PIMANRELATION iman on iman.RPRIMARY_OBJECTU = project.PUID\n" +
            " \t\tLEFT JOIN PFORM fo on fo.puid = iman.RSECONDARY_OBJECTU\n" +
            "\t\tLEFT JOIN PX8_PROJECT_IRSTORAGE ir on ir.puid = fo.RDATA_FILEU\n" +
            "\t\tLEFT JOIN PWORKSPACEOBJECT pwo on pwo.puid = fo.puid\n" +
            "\t\tLEFT JOIN PX8_SHORT_SOLUTION short on short.puid = ir.puid\n" +
            "\t\tLEFT JOIN PX8_LONG_SOLUTION longs on short.puid = ir.puid" +
            "\t\tWHERE ir.puid is not NULL\n" +
            "\t\tAND project.PIS_ACTIVE = '1' " +
            "\t\tAND project.PPROJECT_ID =#{projectId} \n")
    List<ProjectIssues> getIssuesList(String projectId);


    @Select("SELECT DISTINCT\n" +
            "\tpuser.PUSER_NAME username\n" +
            "FROM\n" +
            "\tPTC_PROJECT project\n" +
            "\tLEFT JOIN PPOM_MEMBER member ON member.RGROUPU = project.RPROJECT_TEAMU\n" +
            "\tLEFT JOIN ppom_user puser on puser.PUID = member.ruserU\n" +
            "WHERE\n" +
            "\tproject.PIS_ACTIVE = '1' \n" +
            "\tAND project.PPROJECT_ID = #{projectId}")
    List<ProjectMemberRes> getMembersList(String projectId);
}
